MySQL 性能提升之降龙十八掌

技术老铁们，工作累了，我们就一起来放松一下！老张我呢是个金庸迷，在金庸小说中，降龙十八掌无愧巅峰外功，它的威力之大可想而知。而今儿，老张要给大家介绍18招式，来优化我们的 MySQL 数据库，让它跑起来更快，更稳定！

之前老有学生问我，张老师该如何优化我们的 MySQL 数据库呢？这个问题太泛泛了，不是很具体！因为数据库的优化要从多个角度去考虑，通过不同的维度模型去排查问题。为此我整理了下思路，大概可以从18个角度，大致四个方向去给大家一些建议。

第一掌---亢龙有悔

要想保证数据库能够高效，稳定地运行在服务器上面，我们首先要保证有充足的内存，只有内存足够大了，我们才能缓存住那些我们经常访问的热数据，一些 update 语句的操作当然也可以在内存中优先完成。但是我们要考虑内存使用黄金分割法则，由于不同业务的存在，对内存的需求当然也就不一样了。

举个例子来说，用户经常访问的热数据，对于内存的分配就要尽可能达到达到数据库内存的 70-80% 左右。众所周知，我们知道 MySQL 数据库内存主要靠 innodb_buffer_pool，redo log buffer，double write buffer，binlog cache 等组成。如果服务器上面只跑着 MySQL一个应用，那大概 innodb_buffer_pool 可以分配到物理内存的 50-80% 左右。

TIPS：我们要根据实际物理内存的大小，具体是什么业务类型，去考虑数据库内存的分配。

第二掌---飞龙在天

要优化 MySQL 数据库，首先要很了解对手，随着版本的升级，MySQL 用到的 CPU 核数就越多，自从 MySQL 5.6 之后可以使用到 64 个核。MySQL 连接特点的是这样，每个连接对应一个线程，每个 sql/ 查询只能使用到一个 cpu 核心，所以需要越多的 CPU，并且更快的 CPU。这样才能有利于提高数据库性能，提高我们数据库的并发能力！

TIPS：使用多核 CPU。

第三掌---见龙在田

众所周知，IO 对数据库来说，一直都是瓶颈，并且有可能将来一段时间还会是。所以对存储介质的要求就非常高，对于 IO 系统比较高的情况下，建议我们要使用更快的存储设备 SSD 固态硬盘可提高上百倍的数据读写性能或者是 PCIE-SSD 固态硬盘可提高上千倍的数据读写能力。像现在的一些电商网站，在搞店庆或者促销活动的时候，都需要借助此设备，来满足大量用户的影响请求。

TIPS：建议上高转速硬件设备，SSD 或者 PCIE-SSD

第四掌---鸿渐与陆

自从 web2.0 时代开启，基本所有的，我们使用的软件都是基于 linux 平台自主研发的。我们知道，MySQL 数据库也是跑在 linux 操作系统上面的。在官方建议估计最推荐的是 Solaris，但从实际生产中的角度来看 CentOS 和 REHL 都是不错的选择，个人建议推荐使用 CentOS, 如果非要使用 REHL，建议 版本为6以后的，这里就不推荐使用在 windows 下跑 MySQL 数据库了，虽然随着 MySQL 版本提升，对 windows 有了相关的优化，但是对于高并发，高负载的环境来说，依旧不建议使用。

TIPS：推荐使用 CentOS，或者 REHL 操作系统类型

第五掌---潜龙勿用

操作系统层面的优化，我们要考虑个可能大家会比较忽略的问题，首先就是 swappiness 的问题。swappiness 的值大小对如何使用 swap 分区有着密切的联系。有两个极限值，一个为 0，另一个为 100，查看可执行 cat /proc/sys/vm/swappiness。

0 代表：最大限度地使用物理内存，然后才是 swap 分区，这种行为有可能导致系统内存溢出，从而导致mysql被意外kill掉。不建议这样去设置。

100 则为：积极地使用使用 swap 分区，并且把内存上面的数据及时搬到 swap 分区里。

TIPS：这里比较建议使用默认 60 就可以。

第六掌---利涉大川

与 swappiness 对应的，另一个操作系统层面的优化，还有一个小细节点就是 IO 调度。这里有 cfq，noop 和 deadline，系统默认使用 cfq，这里老师建议使用 deadline。查看方法：

cat /sys/block/sda/queue/scheduler/  

TIPS：deadline 可以调整读写时间，避免写完没有被读取的饿死场景。

第七掌---突如其来

Oracle 11g 之后多了一个 result_cache，来缓存数据结果集。MySQL 里面通过 innodb_buffer_pool 里面有个 query cache 来缓存静态结果集。我们都希望热数据都保存在内存里面，我们读取数据快速便捷，数据库的缓存率也很高！但数据库中的 query cache 里面的数据一但发生更改，此缓存区毫无意义，就会变成鸡肋。而且如果开启 Query Cache，更新与写入都要去检查 query cache 反而增加了写入的开销。

TIPS：建议关闭 query cache

第八掌---震惊百里

对于磁盘阵列，我们再熟悉不过了，但是对于阵列卡的 cache 策略，我们又该如何选择呢。首先对于qps，tps，业务高的系统，一定要配置阵列卡，配 cache 模块，和 BBU 模块（用于提供后备电量）。

cache 策略有两种，一种为：write through（WT）；另一种为：write back；

个人强烈建议使用 write back（WB）。WT含义，数据直接写入磁盘，WB含义：数据先写阵列卡的 cache，再由 cache 写入磁盘，这样对于写入的性能有所提高。并且对于加速 redo log ,binlog, data file都有好处。

TIPS：强烈建议阵列卡的 cache 策略使用 write back。

第九掌---或跃在渊

前面也涉及到了，尽可能大的给 innodb_buffer_pool 分配空间，在服务器只跑数据库一个应用前提下大概为物理内存 50-80%。

TIPS：建议应用与数据库分开部署在服务器上面，后期好排查问题。

第十掌---双龙取水

MySQL 数据库的一些核心参数，我们要在心里铭记。比如双一的含义，直接影响日志的刷新机制。影响 redo log buffer 的刷新机制

innodb_flush_log_at_trx_commit  = 1（最安全）

innodb_flush_log_at_trx_commit  = 2 （性能一般）

innodb_flush_log_at_trx_commit  = 0 （性能最好）。

影响binlog cache的刷新机制~sync_binlog=0，当事务提交之后，MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘，而让 Filesystem 自行决定什么时候来做同步，或者 cache满了之后才同步到磁盘。sync_binlog=n，当每进行 n 次事务提交之后，MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。为了确保安全性，我们可以将 sync_binlog=1。为了获得最佳性能我们可以将 sync_binlog=0。

TIPS：对于不同业务的公司，保障的点不一样，所有我们要考虑好，是业务最重要，还是数据最重要！然后分别去设置不同的参数 value

第十一掌---鱼跃于渊

MySQL 数据库区别于其他数据库最主要就是插件式存储引擎，最为著名就是 myisam 还有 innodb。它们都有各自的特点，这里强烈建议使用 innodb 存储引擎表，无论是对于事务的支持，还是在线 DDL 语句快速操作，它都是目前最优秀的存储引擎！MySQL 5.5 之后默认使用的存储引擎都是 innodb

TIPS：生产环境中，如果还有 myisam 这种存储引擎的表，建议全部做 myisam-->innodb 存储引擎的转换！不过MySQL 5.7之后，系统表也都是 innodb 了！

第十二掌---时乘六龙

文件系统强烈推荐使用 xfs，不要再使用 ext3,ext4 之类的，因为 xfs 这种文件系统也是 B-tree 结构最接近于数据库的树状结构。

第十三掌---密云不雨

生产环境中，经常会出现对大表进行 delete，或者 update 这类的操作。数据碎片随之产生，我们要经常去整理主要业务表的碎片，让查询检索更快。可以通过 pt-ioprofile 监控与磁盘交互最为紧密的表，然后通过 alter table 或者导入导出数据的方法对表进行碎片整理。尽可能回收表空间。

第十四掌---损则有孚

利用天兔（lepus）或者 zabbix 做好对数据库的监控。监控事项可以从服务器的状态，内存的使用情况，cpu的负载。数据库中每秒的增删改查信息，架构中的延迟和复制状态信息去作为监控的核心点。

第十五掌---龙战于野

配合开发人员合理地设计表结构，秉着越简单越好的原则，去选择合适字段的数据类型。对于 ipv4，时间类型的字段，我们完全可以通过整型 int 来存取！通过函数转换就可以了！

   ip涉及到两个函数：inet_aton和inet_ntoa

   时间类型的两个函数：from_unixtime和unix_timestamp

第十六掌---事务隔离级别的选择

mysql数据库中，有四种事务隔离级别。它们分别是Read Uncommitted（RU），Read Committed（RC），Repeatable Read（RR），Serializable（SR）。对于交易类型系统的网站，对于事务要求比较高，我们建议使用RR这种隔离级别。

第十七掌---羝羊触藩

更改文件句柄  ulimit –n 默认1024 太小

   进程数限制  ulimit –u   根据不同版本来决定

   禁掉NUMA  numctl –interleave=all

第十八掌---神龙摆尾

做过数据库的同学们，可以经常会遇到 too many connections 这样的问题，对于这样的问题，我们一定要做好配置数据库内部并发的情况。innodb_thread_concurrency 这个参数来决定 innodb 的并发情况。默认的大小是0。在 mysql5.7 版本中，增加了 thread pool，连接复用的存在，可以取默认值就 ok。但是5.7之前的版本，就需要考量一下取值了，个人建议 mysql5.6版本中设置为 36。mysql5.6 之前可以 8-32。

降龙十八掌已经打完，希望对于数据库爱好者，从事数据库工作中的同学来说有帮助。让我们每天学习一点点，把自己的内功练得越来越深厚，打出属于自己的武功。让我们的数据库飞起来！

作者 | 张甦， 数据库领域的专家和知名人士、图书《MySQL王者晋级之路》作者，51CTO 专家博主。近10年互联网线上处理及培训经验，专注于 MySQL 数据库，对 MongoDB、Redis 等 NoSQL 数据库以及 Hadoop 生态圈相关技术有深入研究，具备非常丰富的理论与实战经验。

https://blog.51cto.com/sumongodb/1949024